Sorts





Kerry Back

Quintile returns

  • At beginning of each time period, sort stocks into quintiles based on a predictor.
  • Could be deciles instead, or …
  • Time periods could be days, weeks, months, … Days would be realistic. Months is easier.
  • Each quintile is a portfolio. Compute return for the period.
  • Repeat next period.

Data

  • Monthly data in SQL database
  • 100+ predictors described in ghz-predictors.xlsx
  • Today, sort on individual predictors
  • Next topic: combine predictors via machine learning

SQL

  • select [columns] from [table]
  • join [another table] on [variables to match on]
  • where [select rows based on conditions]
  • order by [columns to sort on]

Connect with python

from sqlalchemy import create_engine
import pymssql
import pandas as pd

server = "mssql-82792-0.cloudclusters.net:16272"
username = "user"
password = "" # paste password between quote marks
database = "ghz"
string = "mssql+pymssql://" + username + ":" + password + "@" + server + "/" + database
conn = create_engine(string).connect()

Example: ROEQ

data = pd.read_sql(
    """
    select ticker, date, ret, roeq
    from data
    order by date
    """, 
    conn
)
data = data.dropna()
data.head()
ticker date ret roeq
0 EWST 2000-01 -0.044118 0.017047
1 SABC 2000-01 -0.025641 0.027942
2 IROQ 2000-01 -0.008475 0.031816
3 DPAC 2000-01 -0.097276 0.045041
4 SCTT 2000-01 -0.099338 0.061219

Sort into quintiles each month

data["quintile"] = data.groupby("date").roeq.transform(
  lambda x: pd.qcut(x, 5, labels=range(1, 6))
)
data.head()
ticker date ret roeq decile
0 EWST 2000-01 -0.044118 0.017047 3
1 SABC 2000-01 -0.025641 0.027942 3
2 IROQ 2000-01 -0.008475 0.031816 4
3 DPAC 2000-01 -0.097276 0.045041 4
4 SCTT 2000-01 -0.099338 0.061219 5

Compute portfolio returns each month

rets = data.groupby(['date', 'decile']).ret.mean()
rets = rets.unstack()
rets.head()
decile 1 2 3 4 5
date
2000-01 0.199432 0.075519 0.016835 -0.014594 -0.003020
2000-02 0.366746 0.141581 0.043943 0.043550 0.063504
2000-03 -0.065864 0.014373 0.018757 0.036923 0.054467
2000-04 -0.217721 -0.073392 -0.037092 -0.018193 -0.047450
2000-05 -0.134255 -0.056660 -0.020230 -0.025020 -0.044011

Average returns across months

rets.mean()
decile
1    0.010083
2    0.011746
3    0.012197
4    0.012553
5    0.012682
dtype: float64

Compound returns across months

(1+rets).cumprod().plot()